//*********************************************************
//
//    Copyright (c) Microsoft. All rights reserved.
//    This code is licensed under the Apache License, Version 2.0.
//    THIS CODE IS PROVIDED *AS IS* WITHOUT WARRANTY OF
//    ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING ANY
//    IMPLIED WARRANTIES OF FITNESS FOR A PARTICULAR
//    PURPOSE, MERCHANTABILITY, OR NON-INFRINGEMENT.
//
//*********************************************************

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Deployment.WindowsInstaller;
using System.Data.SqlClient;
using System.Management;

namespace TridentManagedCustomActions
{
    public class SQLCustomActions
    {
        private const string SQLConnectionSucess = "1";
        private const string SQLConnectionFailed = "0";
        private const string SQLMasterDatabase = "Master";
        private const string SQLAuthenticationModeSQL = "2";
        private const string SQLDatabaseExistSucess = "1";
        private const string SQLDatabaseExistFailed = "0";

        /// <summary>
        /// session["SQLSERVERNAME"] = <Name of SQL server>
        /// session["SQLSERVERDATABASENAME"] = <Name of SQL server database>
        /// session["SQLSERVERUSERNAME"] = <SQL server user login>
        /// session["SQLSERVERPASSWORD"] = <SQL server user password>
        /// session["SQLSERVERAUTHENTIFICATIONMODE"] = <SQL server Authentication mode>
        /// Create the connection to Master database to check if user can login. 
        /// After that check if the database exists.
        /// </summary>
        /// <param name="session">WIX session properties</param>
        /// <returns>Success if the action was completed. Fails only if unexpected happens</returns>
        [CustomAction]
        public static ActionResult CheckSQLConnection(Session session)
        {
            bool sqlConnectionResult = false;
            bool sqlDatabaseExists = false;

            session["SQLCONNECTIONTESTRESULT"] = SQLConnectionFailed;
            session["SQLDATABASEEXIST"] = SQLDatabaseExistFailed;

            if (IsSQLInstanceValid(session["SQLSERVERNAME"], session["SQLSERVERDATABASENAME"], session["SQLSERVERUSERNAME"], session["SQLSERVERPASSWORD"], session["SQLSERVERAUTHENTIFICATIONMODE"], ref sqlConnectionResult, ref sqlDatabaseExists))
            {
                if (sqlConnectionResult) session["SQLCONNECTIONTESTRESULT"] = SQLConnectionSucess;
                if (sqlDatabaseExists) session["SQLDATABASEEXIST"] = SQLDatabaseExistSucess;
                return ActionResult.Success;
            }
            else
            {
                return ActionResult.Failure;
            }
        }

        /// <summary>
        /// This method will populate the SQL instances only if the integrated connection to the instance exists and Trident datababse do not exist.
        /// </summary>
        /// <param name="session">WIX session handle</param>
        /// <returns>Action result</returns>
        [CustomAction]
        public static ActionResult PopulateLocalSQLInstances(Session session)
        {
            
            // Set the Environment username and Domain name

            session["SYNCLIBUSERNAME"] = Environment.UserName;
            session["SYNCLIBUSERDOMAIN"] = Environment.UserDomainName; 

            List<string> serverinstances = new List<string>();
            try
            {
                DiscoverLocalSQL2005(ref serverinstances);
                DiscoverLocalSQL2008(ref serverinstances);
                int order = 0;
                // Delete the existing contents from the combobox for our property
                View comboBoxDelete = session.Database.OpenView("DELETE FROM ComboBox WHERE Property = 'SQLINSTANCES'");
                comboBoxDelete.Execute();

                // Start adding content for the drop down
                View comboBoxView = session.Database.OpenView("SELECT * FROM ComboBox");
                
                Record newComboBoxRecord = new Record(4);
                session["SQLINSTANCEEXISTS"] = "False";
                foreach (string localinstance in serverinstances)
                {
                    bool sqlConnectionResult = false;
                    bool sqlDatabaseExists = false;
                    IsSQLInstanceValid(localinstance, "Trident", null, null, null, ref sqlConnectionResult, ref sqlDatabaseExists);
                    if (sqlConnectionResult)
                    {
                        newComboBoxRecord = new Record(4);
                        newComboBoxRecord[1] = "SQLINSTANCES";
                        newComboBoxRecord[2] = order++;
                        newComboBoxRecord[3] = localinstance;
                        newComboBoxRecord[4] = localinstance;
                        comboBoxView.Modify(ViewModifyMode.InsertTemporary, newComboBoxRecord);
                        session["SQLINSTANCEEXISTS"] = "True";
                        session["SQLINSTANCES"] = localinstance;
                    }
                }
                if (string.Equals(session["SQLINSTANCEEXISTS"].ToString(), "False"))
                {
                    newComboBoxRecord = new Record(4);
                    newComboBoxRecord[1] = "SQLINSTANCES";
                    newComboBoxRecord[2] = order++;
                    newComboBoxRecord[3] = "Select";
                    newComboBoxRecord[4] = "Select";
                    comboBoxView.Modify(ViewModifyMode.InsertTemporary, newComboBoxRecord);
                    session["SQLINSTANCES"] = "Select";
                }
                    
            }
            catch (Exception ex)
            {
                session.Log("Custom action PopulateLocalSQLInstances Failed : " + ex.Message);
                return ActionResult.Failure;
            }
            return ActionResult.Success;
        }

        /// <summary>
        /// This method will check if connection to SQL server is possible and if the database exits 
        /// </summary>
        /// <param name="serverName">Name of the server or instance</param> 
        /// <param name="databaseName">Name of the datababse</param>
        /// <returns></returns>
        static bool IsSQLInstanceValid(string serverName, string databaseName, string userName, string password, string authenticationMode, ref bool sqlConnectionResult, ref bool sqlDatabaseExists)
        {
            SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder();
            if (!string.IsNullOrEmpty(serverName))
            {
                connectionStringBuilder.DataSource = serverName;
                connectionStringBuilder.InitialCatalog = SQLMasterDatabase;
                connectionStringBuilder.IntegratedSecurity = true;
                if (!string.IsNullOrEmpty(authenticationMode) && authenticationMode == SQLAuthenticationModeSQL)
                {
                    connectionStringBuilder.IntegratedSecurity = false;
                    connectionStringBuilder.UserID = userName;
                    connectionStringBuilder.Password = password;
                }

                using (SqlConnection connection = new SqlConnection(connectionStringBuilder.ConnectionString))
                {
                    try
                    {
                        // Set the default connection value failed.
                        sqlConnectionResult = false;
                        sqlDatabaseExists = false;

                        connection.Open();
                        sqlConnectionResult = true;

                        // check if the database exists
                        SqlCommand sqlCommand = connection.CreateCommand();
                        sqlCommand.CommandText = "SELECT TOP 1 1 FROM sys.databases WHERE name = " + "'" + databaseName + "'";
                        sqlCommand.CommandType = System.Data.CommandType.Text;
                        object result = sqlCommand.ExecuteScalar();

                        if (!(result == null) || (result == DBNull.Value))
                        {
                            // if the records are returned then db exisits
                            sqlDatabaseExists = true;
                        }
                        else
                        {
                            // else db do not exist
                            sqlDatabaseExists = false;
                        }
                    }
                    catch { }
                }
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// This method loads the local SQL instances for SQL server 2008
        /// </summary>
        /// <param name="serverinstances">list of string which is updated with instance names</param>
        static void DiscoverLocalSQL2008(ref List<string> serverinstances)
        {
            try
            {
                ManagementScope instanceScope = new ManagementScope("root\\Microsoft\\SqlServer\\ComputerManagement10");
                ManagementClass instanceClass = new ManagementClass(instanceScope, new ManagementPath("ServerNetworkProtocol"), null);
                instanceClass.Get();
                foreach (ManagementObject instance in instanceClass.GetInstances())
                {
                    instance.Get();
                    string sqlinstance = instance.GetPropertyValue("InstanceName").ToString();
                    if (string.Equals(sqlinstance, "MSSQLSERVER", StringComparison.CurrentCultureIgnoreCase))
                    {
                        sqlinstance = "localhost";
                    }
                    else
                    {
                        sqlinstance = string.Format("Localhost\\{0}", sqlinstance);
                    }
                    if (!serverinstances.Contains(sqlinstance))
                        serverinstances.Add(sqlinstance);
                }
            }
            catch (Exception)
            {

            }
        }

        /// <summary>
        /// This method loads the local SQL instances for SQL server 2005
        /// </summary>
        /// <param name="serverinstances">list of string which is updated with instance names</param>
        static void DiscoverLocalSQL2005(ref List<string> serverinstances)
        {
            try
            {
                ManagementScope instanceScope = new ManagementScope("root\\Microsoft\\SqlServer\\ComputerManagement");
                ManagementClass instanceClass = new ManagementClass(instanceScope, new ManagementPath("ServerNetworkProtocol"), null);
                instanceClass.Get();
                foreach (ManagementObject instance in instanceClass.GetInstances())
                {
                    instance.Get();
                    string sqlinstance = instance.GetPropertyValue("InstanceName").ToString();
                    if (string.Equals(sqlinstance, "MSSQLSERVER", StringComparison.CurrentCultureIgnoreCase))
                    {
                        sqlinstance = "localhost";
                    }
                    else
                    {
                        sqlinstance = string.Format("Localhost\\{0}", sqlinstance);
                    }

                    if (!serverinstances.Contains(sqlinstance))
                        serverinstances.Add(sqlinstance);
                }
            }
            catch (Exception)
            {

            }
        }



    }
}
